package db;

import interfaces.IFProduct;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Customer;
import model.Product;

public class ProductDb implements IFProduct {

	protected Connection con;

	public void connectDb() {
		con = DbConnection.getInstance().getDBcon();
	}

	public void disconnectDb() {
		DbConnection.getInstance();
		DbConnection.closeConnection();
	}
	
	public ProductDb() {
		connectDb();
	}

	@Override
	public Product searchProduct(String name) throws Exception {
		String query = 
				"SELECT * FROM Product " +
				"WHERE name='" + name + "'";
		ResultSet rs = null;
		ArrayList<Product> data = new ArrayList<>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all products ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				data.add(new Product(rs.getInt("productId"), rs.getString("name"), rs.getString("description"), rs.getDouble("purchasePrice"), rs.getDouble("salesPrice"), rs.getString("countryOfOrigin"), rs.getInt("minStock"), rs.getInt("currentStock")));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		if (data.size() == 1)
		{ 
			return data.get(0);
		}
		else
		{
			return new Product();
		}
	}

	@Override
	public ArrayList<Product> getAllProducts() throws Exception {
		String query = 
				"SELECT * FROM Product ";
		ResultSet rs = null;
		ArrayList<Product> data = new ArrayList<>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all products ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				data.add(new Product(rs.getInt("productId"), rs.getString("name"), rs.getString("description"), rs.getDouble("purchasePrice"), rs.getDouble("salesPrice"), rs.getString("countryOfOrigin"), rs.getInt("minStock"), rs.getInt("currentStock")));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

	@Override
	public void insertProduct(String name, String description,
			double purchasePrice, double salesPrice, String countryOfOrigin,
			int minStock, int currentStock) {
		
		String query = null;
		try {
			query = searchProduct(name).getProductId() < 0 ?
					"INSERT INTO Product (name, description, purchasePrice, salesPrice, countryOfOrigin, minStock, currentStock)" +
					"VALUES ('" + name +
					"','" + description +
					"','" + purchasePrice +
					"','" + salesPrice +
					"','" + countryOfOrigin + 
					"','" + minStock + 
					"','" + currentStock + 
					"')" 
					:
						"UPDATE Product SET currentStock='" + currentStock + "' WHERE productId='" + searchProduct(name).getProductId() + "'";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Showing all products ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			//rs = stmt.executeQuery(query);
			
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		
	}

	@Override
	public void removeProduct(String name) throws Exception {
		int productID = searchProduct(name).getProductId();
		String query = 
				"DELETE FROM Product WHERE productId=" + productID + "";
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Deleting all products ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
	}

	@Override
	public void updateStock(int id, int qtty) throws Exception {
		int productID = id;
		String query = 
				"UPDATE Product " +
				"SET currentStock='" + qtty + "'" + 
				"WHERE productId='" + productID + "'";
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Updating product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		
	}

}
